CREATE SERIAL¶

Serial is an object that creates a unique sequence number, and has the following characteristics.

• The serial is useful in creating a unique sequence number in multi-user environment.
• Generated serial numbers are not related with table so, you can use the same serial in multiple tables.
• All users including PUBLIC can create a serial object. Once it is created, all users can get the number by using CURRENT_VALUE and NEXT_VALUE.
• Only owner of a created serial object and DBA can update or delete a serial object. If an owner is PUBLIC, all users can update or delete it.

You can create a serial object in the database by using the CREATE SERIAL statement. Regarding writing serial name, Identifier.

CREATE SERIAL serial_name
[START WITH initial]
[INCREMENT BY interval]
[MINVALUE min | NOMINVALUE]
[MAXVALUE max | NOMAXVALUE]
[CACHE cached_num | NOCACHE]
[COMMENT 'comment_string'];

• serial_identifier: specifies the name of the serial to be generated(maximum: 254 bytes).
• START WITH initial: Specifies the initial value of serial. The range of this value is between -1,000,000,000,000,000,000,000,000,000,000,000,000(-10^36) and 9,999,999,999,999,999,999,999,999,999,999,999,999(10^37-1). The default value of ascending serial is 1 and that of descending serial is -1.
• INCREMENT BY interval: Specifies the increment of the serial. You can specify any integer between -9,999,999,999,999,999,999,999,999,999,999,999,999(-10^37+1) and 9,999,999,999,999,999,999,999,999,999,999,999,999(10^37-1) except zero at interval. The absolute value of the interval must be smaller than the difference between MAXVALUE and MINVALUE. If a negative number is specified, the serial is in descending order otherwise, it is in ascending order. The default value is 1.
• MINVALUE: Specifies the minimum value of the serial. The range of this value is between -1,000,000,000,000,000,000,000,000,000,000,000,000(-10^36) and 9,999,999,999,999,999,999,999,999,999,999,999,999(10^37-1). MINVALUE must be smaller than or equal to the initial value and smaller than the maximum value.
• NOMINVALUE: 1 is set automatically as a minimum value for the ascending serial, -1,000,000,000,000,000,000,000,000,000,000,000,000(-10^36) for the descending serial.
• MAXVALUE: Specifies the maximum number of the serial. The range of this value is between -999,999,999,999,999,999,999,999,999,999,999,999(-10^36+1) and 10,000,000,000,000,000,000,000,000,000,000,000,000(10^37). MAXVALUE must be greater than or equal to the initial value and greater than the minimum value.
• NOMAXVALUE: 10,000,000,000,000,000,000,000,000,000,000,000,000(10^37) is set automatically as a maximum value for the ascending serial, -1 for the descending serial.
• CYCLE: Specifies that the serial will be generated continuously after reaching the maximum or minimum value. When a serial in ascending order reaches the maximum value, the minimum value is created as the next value; when a serial in descending order reaches the minimum value, the maximum value is created as the next value.
• NOCYCLE: Specifies that the serial will not be generated anymore after reaching the maximum or minimum value. The default value is NOCYCLE.
• CACHE: Stores as many serials as the number specified by "cached_num" in the cache to improve the performance of the serials and fetches a serial value when one is requested. If all cached values are used up, as many serials as "cached_num" are fetched again from the disk to the memory. If the database server stops accidently, all cached serial values are deleted. For this reason, the serial values before and after the restart of the database server may be discontinuous. Because the transaction rollback does not affect the cached serial values, the request for the next serial will return the next value of the value used (or fetched) lastly when the transaction is rolled back. The "cached_num" after the CACHE keyword cannot be omitted. If the "cached_num" is equal to or smaller than 1, the serial cache is not applied.
• NOCACHE: Specifies that the serial cache is not used, and serial value is updated for each time.
• comment_string: specifies a comment of a serial.

--creating serial with default values
CREATE SERIAL order_no;

--creating serial within a specific range
CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;

--creating serial with specifying the number of cached serial values
CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000 CACHE 3;

--selecting serial information from the db_serial class
SELECT * FROM db_serial;

  name            current_val      increment_val         max_val         min_val         cyclic      started       cached_num        att_name
====================================================================================================================================================
'order_no'      10006            2                     20000           10000                0            1                3            NULL

The following example shows how to create the athlete_idx table to store athlete codes and names and then create an instance by using the order_no. NEXT_VALUE increases the serial number and returns its value.

CREATE TABLE athlete_idx( code INT, name VARCHAR(40) );
CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Park');
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Kim');
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Choo');
INSERT INTO athlete_idx VALUES (order_no.CURRENT_VALUE, 'Lee');

SELECT * FROM athlete_idx;

         code  name
===================================
        10000  'Park'
        10002  'Kim'
        10004  'Choo'
        10004  'Lee'

CREATE SERIAL order_no
START WITH 100 INCREMENT BY 2 MAXVALUE 200
COMMENT 'from 100 to 200 by 2';

SELECT name, comment FROM db_serial;

ALTER SERIAL¶

With the ALTER SERIAL statement, you can update the increment of the serial value, set or delete its initial or minimum/maximum values, and set its cycle attribute.

ALTER SERIAL serial_identifier
[INCREMENT BY interval]
[START WITH initial_value]
[MINVALUE min | NOMINVALUE]
[MAXVALUE max | NOMAXVALUE]
[CACHE cached_num | NOCACHE]
[COMMENT 'comment_string'];

• serial_identifier: specifies the name of the serial to be created(maximum: 254 bytes).
• INCREMENT BY interval: specifies the increment of the serial. For the interval, you can specify any integer with 38 digits or less except zero. The absolute value of the interval must be smaller than the difference between MAXVALUE and MINVALUE. If a negative number is specified, the serial is in descending order; otherwise, it is in ascending order. The default value is 1.
• START WITH initial_value: changes the initial value of Serial.
• MINVALUE: specifies the minimum value of the serial with 38 digits or less. MINVALUE must be smaller than or equal to the initial value and smaller than the maximum value.
• NOMINVALUE: 1 is set automatically as a minimum value for the ascending serial; -(10) 36 for the descending serial.
• MAXVALUE: specifies the maximum number of the serial with 38 digits or less. MAXVALUE must be larger than or equal to the initial value and greater than the minimum value.
• NOMAXVALUE: (10) 37 is set automatically as a maximum value for the ascending serial; -1 for the descending serial.
• CYCLE: specifies that the serial will be generated continuously after reaching the maximum or minimum value. If the ascending serial reaches the maximum value, the minimum value is generated as the next value. If the descending serial reaches the minimum value, the maximum value is generated as the next value.
• NOCYCLE: specifies that the serial will not be generated anymore after reaching the maximum or minimum value. The default is NOCYCLE.
• CACHE: stores as many serials as the number specified by integer in the cache to improve the performance of the serials and fetches a serial value when one is requested. The integer after the CACHE keyword cannot be omitted. If a number equal to or smaller than 1 is specified, the serial cache is not applied.
• NOCACHE: It does not use the serial cache feature. The serial value is updated every time and a new serial value is fetched from the disk upon each request.
• comment_string: specifies a comment of a serial.

--altering serial by changing start and incremental values
ALTER SERIAL order_no START WITH 100 MINVALUE 100 INCREMENT BY 2;

--altering serial to operate in cache mode
ALTER SERIAL order_no CACHE 5;

--altering serial to operate in common mode
ALTER SERIAL order_no NOCACHE;

CREATE SERIAL s1;
SELECT s1.NEXTVAL;

ALTER SERIAL s1 START WITH 10;

SELECT s1.NEXTVAL;
-- From 9.0, above query returns 10
-- In the version less than 9.0, above query returns 11

The below changes the comment of the serial.

ALTER SERIAL order_no COMMENT 'new comment';

DROP SERIAL¶

With the DROP SERIAL statement, you can drop a serial object from the database. If you also specify IF EXISTS clause, no error will be happened even if a target serial does not exist.

DROP SERIAL [ IF EXISTS ] serial_identifier ;

• serial_identifier: Specifies the name of the serial to be dropped.

The following example shows how to drop the order_no serial.

DROP SERIAL order_no;
DROP SERIAL IF EXISTS order_no;

Accessing Serial¶

serial_identifier.CURRENT_VALUE
serial_identifier.NEXT_VALUE

CREATE TABLE athlete_idx (code INT, name VARCHAR (40));
CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Park');
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Kim');
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Choo');
INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Lee');
SELECT * FROM athlete_idx;

         code  name
===================================
        10000  'Park'
        10002  'Kim'
        10004  'Choo'
        10006  'Lee'

CREATE SERIAL order_no START WITH 101 INCREMENT BY 1 MAXVALUE 20000;
SELECT SERIAL_CURRENT_VALUE(order_no);

101

-- At first, the first serial value starts with the initial serial value, 10000. So the l0'th serial value will be 10009.
SELECT SERIAL_NEXT_VALUE(order_no, 10);

110

SELECT SERIAL_NEXT_VALUE(order_no, 10);

120